MYDB=$DB_ELECTIONS
INPUT_FOLDER=../data/raw_data/election_results
OUTPUT_FOLDER=../data/data_csv

mkdir -p $INPUT_FOLDER

#################################################################################################################################
########## DOWNLOAD INPUT (RAW) FILES MADE PUBLICLY AVAILABLE BY THE GOVERNMENT ##########
CUR_FOLDER=${PWD}
cd $INPUT_FOLDER

### Download file of election results (presidential election of 2012)
curl https://www.data.gouv.fr/s/resources/election-presidentielle-2012-resultats-par-bureaux-de-vote-1/20150925-102751/PR12_Bvot_T1T2.txt --output PR12_Bvot_T1T2.txt

cd $CUR_FOLDER
#################################################################################################################################

# Import file with election results
ELECTIONS_FILE=$INPUT_FOLDER/PR12_Bvot_T1T2.txt
ELECTIONS_FILE2=$INPUT_FOLDER/PR12_Bvot_T1T2b.txt
python3 addHeaderLine2File.py $ELECTIONS_FILE $ELECTIONS_FILE2 "NumTour;DEP;COM;LIBCOM;var1;var2;NumBureauVote;Inscrits;Votants;Exprimes;NumCandidat;NomCandidat;PrenomCandidat;CodeNuanceCandidat;VoixCandidat"
importCSVFile2 $MYDB $ELECTIONS_FILE2 PR12 ";"
rm $ELECTIONS_FILE2

# Get CodeInsee
sqlite3 $MYDB "create table a1 as \
    select NumTour, DEP || COM as CodeInsee, LIBCOM, NumBureauVote, Inscrits, Votants, Exprimes, 
        NumCandidat, NomCandidat, PrenomCandidat, CodeNuanceCandidat, VoixCandidat \
    from PR12;"

# Merge a2 and all_communes
sqlite3 $MYDB "create table a2 as \
    select NumTour, b.POLE as CodeInsee, NumBureauVote, Inscrits, Votants, Exprimes, NumCandidat, NomCandidat,  \
        PrenomCandidat, CodeNuanceCandidat, VoixCandidat \
    from a1 a, all_communes b \
    where a.CodeInsee = b.CodeInsee;"
# NOTE: This removes DOM/TOM's and votants outside of France.
sqlite3 $MYDB "update a2 set CodeInsee = 49160 where CodeInsee = 49382;"
sqlite3 $MYDB "update a2 set CodeInsee = 51171 where CodeInsee = 51664;"
dropTables $MYDB PR12 a1

# Aggregate at CodeInsee level
sqlite3 $MYDB "create table a3 as 
    select NumTour, CodeInsee, NumCandidat, NomCandidat, PrenomCandidat, CodeNuanceCandidat, \
    sum(VoixCandidat) as VoixCandidat, sum(Inscrits) as Inscrits, sum(Votants) as Votants, sum(Exprimes) as Exprimes \
    from a2 \
    group by NumTour, CodeInsee, NumCandidat, NomCandidat, PrenomCandidat, CodeNuanceCandidat;"

# Reduce data to results of 1st round only
sqlite3 $MYDB "delete from a3 where NumTour != 1;"

# Put results horizontally
selectDistinct $MYDB 1 ElectionResults a3 CodeInsee Inscrits Votants Exprimes
NOMS_CANDIDATS=( ARTHAUD BAYROU CHEMINADE DUPONT-AIGNAN HOLLANDE JOLY "LE PEN" MÉLENCHON POUTOU SARKOZY )
for ii in $(seq 0 $(( ${#NOMS_CANDIDATS[@]} - 1))); do
    NOM_CAND=${NOMS_CANDIDATS[$ii]}
    echo $NOM_CAND
    NOM_CAND2=${NOM_CAND/É/E}
    NOM_CAND2=${NOM_CAND2/" "/""}
    NOM_CAND2=${NOM_CAND2/"-"/""}
    
    sqlite3 $MYDB "create table a4 as \
        select CodeInsee, VoixCandidat as Voix_$NOM_CAND2, Inscrits, Votants, Exprimes  \
        from a3 \
        where NumTour = 1 and NomCandidat = '$NOM_CAND';"
    
    sqlite3 $MYDB "create table a5 as \
        select a.*, b.Voix_$NOM_CAND2 \
        from ElectionResults a left join a4 b \
        on a.CodeInsee = b.CodeInsee;"
    dropTables $MYDB a4 ElectionResults
    renameTable $MYDB a5 ElectionResults
done

leftJoin $MYDB 1 ElectionResults2 mycommunes ElectionResults CodeInsee
dropTables $MYDB ElectionResults a2 a3

# Merge with Scope + calculate percentages
mysql="create table ElectionResults_CantonId as \
    select a.CantonId as CantonId, sum(Inscrits) as ElecteursInscrits, sum(Votants) as Votants, sum(Exprimes) as VotesExprimes "
for ii in $(seq 0 $(( ${#NOMS_CANDIDATS[@]} - 1))); do
    NOM_CAND=${NOMS_CANDIDATS[$ii]}
    NOM_CAND2=${NOM_CAND/É/E}
    NOM_CAND2=${NOM_CAND2/" "/""}
    NOM_CAND2=${NOM_CAND2/"-"/""}
    mysql+=", sum(Voix_$NOM_CAND2) as Voix_$NOM_CAND2, 1.0*sum(Voix_$NOM_CAND2)/sum(Exprimes) as PctageVoix_$NOM_CAND2"
done
mysql+=" from Scope_CantonId a left join ElectionResults2 b on a.CantonId = b.CantonId \
    group  by a.CantonId;"
sqlite3 $MYDB "$mysql"
sqlite3 $MYDB "update ElectionResults_CantonId set ElecteursInscrits = 0 where ElecteursInscrits is null;"
sqlite3 $MYDB "update ElectionResults_CantonId set Votants = 0 where Votants is null;"
sqlite3 $MYDB "update ElectionResults_CantonId set VotesExprimes = 0 where VotesExprimes is null;"
for ii in $(seq 0 $(( ${#NOMS_CANDIDATS[@]} - 1))); do
    NOM_CAND=${NOMS_CANDIDATS[$ii]}
    NOM_CAND2=${NOM_CAND/É/E}
    NOM_CAND2=${NOM_CAND2/" "/""}
    NOM_CAND2=${NOM_CAND2/"-"/""}
    sqlite3 $MYDB "update ElectionResults_CantonId set Voix_$NOM_CAND2 = 0 where Voix_$NOM_CAND2 is null;"
    sqlite3 $MYDB "update ElectionResults_CantonId set PctageVoix_$NOM_CAND2 = 0 where PctageVoix_$NOM_CAND2 is null;"
done

# Clean up
dropTables $MYDB ElectionResults2
dropTables $MYDB Scope_CantonId all_communes mycommunes
sqlite3 $MYDB "VACUUM;"
